#!/usr/bin/env python
# Copyright(c) 2010 Nuttachat Wisittipanit. All rights reserved.

from mahbo.lib import (GUTIBD,)
from mahbo.information.database.utility.dbinterface import DatabaseInterface
from mahbo.information.dataprocess.report.featurereport import FeatureReport
from mahbo.information.dataprocess.report.formalreport import FormalReport
from mahbo.information.dataprocess.report.cpreport import ComparisonReport
from mahbo.information.dataprocess.report.svmreport import SVMReport
from mahbo.information.dataprocess.report.mstatreport import MSTATReport
from mahbo.information.dataprocess.gutmtps.mtpsutils import RDPLevel
from mahbo.information.dataprocess.gutmtps.gutlhpcr import GUTMTPSLHPCR
from mahbo.utility.utility import (RESULTS,)

from minings import (SVMpm,Mpm,)
from minings.classification.svm.main import ProcessSVM
from minings.factory.datafactory import DataFactory
from minings.stat.metastats.main import ProcessMetastats
from minings.utility.utility import (DatasetTools,RUN,)

from copy import deepcopy
import os,orange,MySQLdb,xlwt,xlrd,string,cPickle,math,numpy
import psyco
psyco.full() #Speeding Up Python

Abr     = GUTIBD.Abr.main
Eight   = GUTIBD.rdpType.Eight
Ten     = GUTIBD.rdpType.Ten


class GUTMTPS(DatabaseInterface):

    def __init__(self,**kwargs):
        self.maindbname                 = 'gutmtps'
        self.pthreshold                 = 0.08
        self.flow                       = None #FLOW instance
        self.__dict__.update(kwargs)
        self.rdptype                    = {} #{'8': RDPInfo instance, }
        #Set connection DatabaseInterface class
        #True means connect to orngMySQL database also (self.t)
        DatabaseInterface.__init__(self,self.maindbname,True)
        self.addConnect('gut')
        #Get information about all excel source files
        self.getXcelProperty()
        self.getGutLHPCRdata()
        
    def getXcelProperty(self):
        self.svmoutput_file             = 'svm_gutmtps.xls'
        self.mstatoutput_file           = 'mstat_gutmtps.xls'
        self.foutput_file               = 'feature_gutmtps.xls'
        self.moutput_file               = 'formal_gutmtps.xls'
        self.flookup_file               = 'feature_lookup.xls'
        self.coutput_file               = 'comparison_gutmtps.xls'
        self.calloutput_file            = 'comparison_mtps_lhpcr.xls'
        #XCel Read properties
        #IBD_All_RDP10_table_X -> MTPS data of every RDP level, Sample names has Fingerprint ID
        self.data_folder                = GUTIBD.Folder.Gutmtps.mtpsraw
        self.rdp8_abd_sfile             = 'IBD_RDP8_Abundance_Table_X.xls'
        self.rdp10_abd_sfile            = 'IBD_RDP10_Abundance_Table_X.xls'
        self.rdp8_abd_file              = os.path.join(self.data_folder,self.rdp8_abd_sfile)
        self.rdp10_abd_file             = os.path.join(self.data_folder,self.rdp10_abd_sfile)
        #Setup RDPInfo
        for rdptype in GUTIBD.rdpType.LIST:
            self.rdptype[rdptype]       = RDPInfo(rdpType = rdptype)
        #Open rdp10_file
        self.rdptype[Eight].abd_book    = xlrd.open_workbook(self.rdp8_abd_file) 
        self.rdptype[Ten].abd_book      = xlrd.open_workbook(self.rdp10_abd_file)
        #'IBD_All_RDP10_table_X' has 2 sheets, rawdata and Sample Sites
        self.rdptype[Eight].abd_sheet   = self.rdptype[Eight].abd_book.sheet_by_index(0) 
        self.rdptype[Ten].abd_sheet     = self.rdptype[Ten].abd_book.sheet_by_index(0)
        #'Sample Sites' sheet
        self.ssites_sheet               = self.rdptype[Eight].abd_book.sheet_by_index(1)
        #Rush_Only_MasterCOC_012510_OUT_X -> Metadata of Fingerprint ID, diseases,age,sex
        self.meta_sfile                 = 'IBD_Metadata_X.xls'
        self.meta_file                  = os.path.join(self.data_folder,self.meta_sfile)
        self.metabook                   = xlrd.open_workbook(self.meta_file)
        #'Rush_Only_MasterCOC_012510_OUT' has only one sheet
        self.meta_sheet                 = self.metabook.sheet_by_index(0)
        #Xcel Write properties
        self.output_folder              = GUTIBD.Folder.Gutmtps.mtpsout
        #Write to this file
        self.rdptype[Eight].outfile     = os.path.join(self.output_folder,'GUTMTPS_Information_8.xls') 
        self.rdptype[Ten].outfile       = os.path.join(self.output_folder,'GUTMTPS_Information_10.xls')
        self.rdptype[Eight].bookwrite   = xlwt.Workbook() 
        self.rdptype[Ten].bookwrite     = xlwt.Workbook()
        self.col_width                  = 5000
        self.rdptype[Eight].gutmtps     = self 
        self.rdptype[Ten].gutmtps       = self

    def getGutLHPCRdata(self):
        #Get SampleName from table gut from gut database
        self.adb['gut'].query("""select %s from %s
                              """ % ('m$Sample_Name','gut'))
        row                     = self.adb['gut'].cursor.fetchall()
        self.lhpcrSampleName    = [u['m$Sample_Name'] for u in row]
        #Get x lhpcrSampleName
        #.useSampleName -> [('Dys100_37_PCR2_103_61','Dys100_37'),..]
        self.useSampleName      = []
        #.Sample -> ['Dys100_37','Dys100_19','PG3_45',..]
        self.sSample            = [] 
        #lhpcrSampleName has a weird name, ex. 'Dys100_37_PCR2_103_61' means 'Dys100_37' 
        for i in self.lhpcrSampleName:            
            #i -> 'Dys100_37_PCR2_103_61' or 'PG_03_PS_0_15'
            a   = i.find('_')
            if a == -1:
                continue
            b   = i.find('_',a+1)
            if b == -1:
                #if i -> 'PG3_45', usename = 'PG3_45'
                usename = i
            else:
                #if i -> 'Dys100_37_PCR2_103_61', usename = 'Dys100_37'
                usename = i[:b]
            try:
                self.sSample.index(usename)
            except (ValueError,):
                self.sSample.append(usename)
                self.useSampleName.append((i,usename))
    
    def doMain(self):
        #{'RDP LEVEL 1': RDPLevel instance, ..}
        #Tell whether to generate new data or not
        isDataExist     = True
        for rdptype in GUTIBD.rdpType.LIST:
            for rdp in GUTIBD.rdpList.LIST:
#                if rdptype != '8' or rdp != 'RDP LEVEL 7': 
#                    continue
                #rdptype -> '8'
                #rdp     -> 'RDP LEVEL 1', rdpDbName -> 'rdp8level1'
                rdpDbName                   = self.rdp2dbName(rdp,rdptype)
                rdpDbNameM                  = '%sm' % rdpDbName
                try:
                    data                    = self.t.query("""SELECT * FROM %s""" % rdpDbName)            
                    data                    = DatasetTools.finalBrush(data)
                    self.rdptype[rdptype].rdplevel[rdp] = RDPLevel(maindbname = self.maindbname   ,
                                                                   level        = rdp               ,
                                                                   rdpDbName    = rdpDbName         ,
                                                                   rdpDbNameM   = rdpDbNameM        ,
                                                                   maindata     = data              ,
                                                                   )
                    if len(data):
                        self.rdptype[rdptype].rdplevel[rdp].getRawdataMySQL()
                except (MySQLdb.ProgrammingError,):
                    isDataExist             = False
                    break #if data exist not all level do all 
            if not isDataExist:
                break
        #Then data in self.rdpType[rdptype].rdplevel[rdp] must spawn several datasets 
        #according to various sites
        if not isDataExist:
            #If data is missing, delete them first
            self.clearAllTable()
            #if required to make data, reset rdplevel
            for i in (GUTIBD.rdpType.Eight,GUTIBD.rdpType.Ten,):
                self.rdptype[i].rdplevel.clear()
                #Transfrom raw data to orange data and keep it in database
                self.rdptype[i].data2orange()
                for rdp in self.rdptype[i].RDP:
                    self.rdptype[i].rdplevel[rdp]   = self.rdptype[i].RDP[rdp]
                    if len(self.rdptype[i].RDP[rdp].preSample) != 0:
                        self.rdptype[i].rdplevel[rdp].toOrangeTable()
                    else:
                        #in case of rdp10level7 only
                        continue
                    #rdp         -> 'RDP LEVEL 1'
                    #self.rdptype[i].RDP[rdp] -> RDPLevel instance
                    rdpDbName                                   = self.rdptype[i].RDP[rdp].rdpDbName
                    #rdpDbNameM  -> 'rdp8level1m'
                    self.rdptype[i].rdplevel[rdp].rdpDbNameM    = '%sm' % rdpDbName                    
                    data                                        = self.rdptype[i].RDP[rdp].maindata
                    #Write data into database
                    self.t.write(rdpDbName,data,overwrite = True)  
                    #Write feature lookup table
                    index2feature   = self.rdptype[i].rdplevel[rdp].index2feature
                    self.writeFeatureLookup(rdpDbName,index2feature)
            self.createFakeTable('rdp10level1','rdp10level7')  
            self.modifyTables()
            for rdptype in GUTIBD.rdpType.LIST:
                for rdp in GUTIBD.rdpList.LIST:
                    data    = self.rdptype[rdptype].rdplevel[rdp].maindata
                    try:
                        len(data)
                    except (TypeError,):
                        continue
                    if len(data) == 0:
                        #Incase of rdp10level7 
                        continue
                    self.rdptype[rdptype].rdplevel[rdp].getRawdataMySQL()
    
    def newNormalize(self):
        for rdptype in GUTIBD.rdpType.LIST:
            for rdp in GUTIBD.rdpList.LIST:
                tlist           = []
                rdpDbName       = self.rdp2dbName(rdp,rdptype)
                tlist.append(rdpDbName)
                reg             = '^%s_' % rdpDbName
                self.RD.query("""select * from %s where %s regexp "%s" 
                              """ % (self.RD.tab_info.tablename,self.RD.tab_info.name,reg) )
                #In this case, it will be like [{'m$Sample_Site':'1'},{'m$Sample_Site':'2'}, .. ]
                row             = self.RD.cursor.fetchall() 
                #Check whether I have everything ready to construct a datapackage
                for i in row:
                    #dataname -> 'rdplevel1_ileum'
                    dataname    = i[self.RD.tab_info.name]
                    tlist.append(dataname)
                for tbnamex in tlist:
                    tbname          = tbnamex.lower()
                    data            = self.t.query("""select * from %s""" % tbname) 
                    data            = DatasetTools.finalBrush(data) 
                    da              = data.toNumpy('ac')[0]
                    gg              = da[:,:-1]
                    c               = da[:,-1]
                    for i in range(len(gg)):
                        ss      = numpy.sum(gg[i]**2)
                        r       = math.sqrt(ss)
                        gg[i]   = gg[i]/float(r)    
                    gy              = gg.T
                    ga              = numpy.vstack((gy,c))
                    gu              = ga.T
                    #Get meta data
                    #a -> {-1:'pname',-2:'c',..}
                    a               = data.domain.getmetas()
                    meta            = {} #b = { 'pname':['Joe','Ann'],'c':['US','CA','UK'] }
                    for i in a:
                        meta[a[i]]  = []
                        for j in data:
                            meta[a[i]].append(j[a[i]])
                    classValues     = data.domain.classVar.values
                    k               = orange.EnumVariable(data.domain.classVar.name,values = classValues)
                    dm              = orange.Domain(data.domain.attributes,k)
                    for i in meta:
                        dm.addmeta(data.domain.metaid(i),orange.StringVariable(i))
                    datatemp        = orange.ExampleTable(dm,gu)
                    for i in meta:
                        for j in range(len(data)):
                            datatemp[j][i] = meta[i][j]
                    self.t.write(tbname,datatemp,overwrite = True)                    
                    
    def writeDatasetInformation(self):
        for rdptype in GUTIBD.rdpType.LIST:
            #rdptype -> '8'            
            bookwrite           = self.rdptype[rdptype].bookwrite 
            outfile             = self.rdptype[rdptype].outfile            
            for rdp in GUTIBD.rdpList.LIST:                
                #Add sheet 'RDP LEVEL 1'               
                #.maindata -> data for 'rdp8level1' or 'rdp10level6' 
                rdpDbName       = self.rdp2dbName(rdp,rdptype)
                data            = self.t.query("""select * from %s
                                               """ % (rdpDbName,))    
                data            = DatasetTools.finalBrush(data)            
                try:
                    len(data)
                except (TypeError,):
                    continue
                if len(data) == 0:
                    #Incase of rdp10level7 
                    continue
                #name -> 'RDP LEVEL 1'
                name            = rdp
                sheet           = bookwrite.add_sheet(name)
                for i in range(6):
                    sheet.col(i).width = self.col_width
                #I need 'rdp8level1_ileum','rdp8level1_colon','rdp8level1_sigmoid',
                #       'rdp8level1_lumen','rdp8level1_entire'
                #siteinfo -> {'ileum': {'Crohn': 10,'Ulcerative Colitis': 12,'Healthy Control': 15,'Total': 20}, ..}
                #Collect Information 
                numfeatures     = len(data.domain.attributes)
                siteinfo        = {}
                for site in GUTIBD.site.LIST:
                    #sited -> {'Crohn': 10,'Ulcerative Colitis': 12,'Healthy Control': 15,'Total': 20}
                    sited               = {}
                    #site -> 'ileum',sitename -> 'rdp8leve1_ileum'                    
                    sitename            = '%s_%s' % (rdpDbName,site)
                    sitedata            = self.t.query("""select * from %s
                                                       """ % sitename)            
                    sitedata            = DatasetTools.finalBrush(sitedata)
                    for cls,number in zip(sitedata.domain.classVar.values,
                                      orange.Distribution(sitedata.domain.classVar,sitedata)):
                        sited[cls]      = number
                    sited['Total']      = len(sitedata)
                    siteinfo[site]      = deepcopy(sited)                
                #Write Information
                self.c_col  = 0
                self.c_row  = 0
                sheet.row(self.c_row).write(self.c_col,'Disease/Location')
                row         = self.c_row + 1
                for disease in GUTIBD.disease.LIST:
                    sheet.row(row).write(self.c_col,disease)
                    row         += 1
                sheet.row(row).write(self.c_col,'Total')
                self.c_col      = 1
                self.c_row      = 0
                row             = self.c_row 
                for i in GUTIBD.site.LIST:            
                    #i -> 'ileum'
                    usesiteName     = i[0].upper() + i[1:]        
                    sheet.row(self.ri()).write(self.c_col,usesiteName)
                    dist            = GUTIBD.disease.LIST + ('Total',)
                    for j in dist:
                        number      = siteinfo[i][j]
                        sheet.row(self.ri()).write(self.c_col,number)
                    #Set row back to the same state
                    self.c_row    = row
                    #Increate Column by 1
                    self.ci()
                sheet.row(6).write(0,'Number of Features')
                sheet.row(6).write(1,numfeatures)
            bookwrite.save(outfile)
    
    def writeFeatureLookup(self,rdpDbName,index2feature):
        tablename   = '%s_fl' % (rdpDbName)
        self.RD.addFLookup(tablename)
        keysort     = sorted(index2feature.keys())
        for i in keysort:
            #i -> 'A_1'
            self.RD.query("""insert into %s values ("%s","%s")
                          """ % (tablename,i,index2feature[i])) 
    
    def createFakeTable(self,rdpDbName,rdpDbFake):
        #Create fake table rdp10level7 from rdp10level1
        #Copy table first
        self.RD.query("""create table %s like %s
                      """ % (rdpDbFake,rdpDbName))
    
    def clearAllTable(self):
        #Drop all databases
        self.RD.query("""drop database %s
                      """ % self.maindbname)
        #Reconnect and create everything again
        DatabaseInterface.__init__(self,self.maindbname,True)
    
    def modifyTables(self):
        for rdptype in GUTIBD.rdpType.LIST:
            for rdp in GUTIBD.rdpList.LIST:
                rdpDbName   = self.rdp2dbName(rdp,rdptype)
                tablecopy   = '%sm' % rdpDbName
                self.modifyTablesX(rdpDbName,tablecopy)
    
    def modifyTablesX(self,rdpDbName,tablecopy):
        #Copy table first
        self.RD.query("""create table %s like %s
                      """ % (tablecopy,rdpDbName))
        self.RD.query("""insert %s select * from %s
                      """ % (tablecopy,rdpDbName))
        #Query for each Samplename
        samplename  = []
        self.RD.query("""select * from %s
                      """ % (rdpDbName)
                      )
        row         = self.RD.cursor.fetchall()
        if row in (None,()):
            return
        for r in row:
            disease = r['c$Disease']
            classx  = r['m$ClassX']
            sname   = r['m$SampleName']
            samplename.append((disease,classx,sname))
        self.RD.query("""alter table %s modify %s TEXT
                      """ % (tablecopy,"c$Disease") )
        d_list      = []
        for d,c,s in samplename:
            if c != '':
                d_modified = '%s_%s' % (d,c)    
            else:
                d_modified = d  
            try:
                d_list.index(d_modified)
            except ValueError:
                #Not before
                d_list.append(d_modified)
            #Modify as such 'Crohn' -> 'Crohn_Ileum' where Ileum is identified by 'm$ClassX'
            self.RD.query("""update %s set %s="%s" where %s="%s"
                          """ % (tablecopy,'c$Disease',d_modified,'m$SampleName',s)
                          )
        a           = string.join(d_list,"','")
        classList   = "('%s')" % a
        self.RD.query("""alter table %s modify %s ENUM%s
                              """ % (tablecopy,"c$Disease",classList) )
    
    def rdp2dbName(self,rdp,rdptype):
        rdpname = rdp.replace(' ','').lower()
        rdpname = rdpname.replace('rdp','rdp%s' % rdptype)
        return rdpname
    
    def ci(self):
        self.c_col += 1
        #Increase and return current column
        return self.c_col - 1
    
    def ri(self):
        self.c_row += 1
        return self.c_row - 1
    
    def processDataFactory(self):
        for i in GUTIBD.rdpType.LIST:
            #i -> '8'
            for j in GUTIBD.rdpList.LIST:
                try:
                    self.rdptype[i].rdplevel[j]
                except (KeyError,):
                    continue
                #j -> 'RDP LEVEL 1'
                for k in self.rdptype[i].rdplevel[j].rawdata:
                    #k -> 'Entire','Ileum' or 'Ileum_Lumen'
                    #.rawdata[k] -> GutMTPSDataPackage instance
                    if len(self.rdptype[i].rdplevel[j].rawdata) == 0:
                        #Filter out invalid type and level
                        continue
                    for u in self.rdptype[i].rdplevel[j].rawdata[k].datapackages:
                        #j -> DataPackage instance
                        dname       = u.datasetname
                        doDatafact  = True
                        #Upload information for a dataset 'rdp10level1_Entire_Crohn_Healthy Control'
                        self.RD.query("""select * from %s where %s="%s"
                                      """ % (self.RD.tab_results.tablename      ,
                                             self.RD.tab_results.datasetname    ,
                                             dname                              ,))
                        row         = self.RD.cursor.fetchone()
                        if row in (None,()):
                            #If dataset do not exist,mark as freshy    -> True
                            freshy      = True
                        else:        
                            freshy      = False
                            result_obj  = cPickle.loads(row[self.RD.tab_results.result_obj])
                            datafact    = result_obj.datafact   
                            if datafact == None:
                                doDatafact     = True
                            else:
                                #DataFactory instance exists
                                if self.flow.REdatafact:
                                    #if flow.REdatafact -> True,redo it
                                    doDatafact = True
                                else:
                                    doDatafact = False
                        if doDatafact:
                            df = DataFactory(datapackage = u                 ,                                                                                                       
                                             run         = RUN()             ,
                                             )
                            df.getDataPipeline()
                        else:
                            continue
                        if freshy:
                            #in anycase,if freshy -> True,do anew anyway (only record DataFactory instance)
                            result_obj          = RESULTS()
                            result_obj.datafact = df
                            result_obj_string   = MySQLdb.escape_string(cPickle.dumps(result_obj,2))
                            self.RD.query("""insert into %s (%s,%s) values  ("%s","%s")
                                          """ % (self.RD.tab_results.tablename      ,    
                                                 self.RD.tab_results.datasetname    ,
                                                 self.RD.tab_results.result_obj     ,
                                                 dname                              ,
                                                 result_obj_string                  ,))
                        else:
                            #freshy -> False,doDatafact -> True,come in here
                            result_obj.datafact = df
                            result_obj_string   = MySQLdb.escape_string(cPickle.dumps(result_obj,2))
                            self.RD.query("""update %s set %s="%s" where %s="%s"
                                          """ % (self.RD.tab_results.tablename      ,
                                                 self.RD.tab_results.result_obj     ,
                                                 result_obj_string                  ,
                                                 self.RD.tab_results.datasetname    ,
                                                 dname                              ,))
    
    def gatherRecordInformation(self):
        #self.mset -> {'rdp8level1': MainSet instance, ..}
        self.mset       = {}
        for rdptype in GUTIBD.rdpType.LIST:
            #i -> '8'
            for rdp in GUTIBD.rdpList.LIST:
                #rdpDbName -> 'rdp8level1
                rdpDbName   = self.rdp2dbName(rdp,rdptype)                                                              
                #Find all table names begining with 'rdp8level1_'
                # 'rdp8level1_Entire__Crohn__Healthy Control' and 
                # 'rdp8level1_Entire__Healthy Control__Ulcerative Colitis' are grouped together
                reg         = '^%s_' % (rdpDbName,)
                self.RD.query("""select * from %s where %s regexp "%s" 
                              """ % (self.RD.tab_results.tablename      ,
                                     self.RD.tab_results.datasetname    ,
                                     reg                                ,
                                     ))        
                row         = self.RD.cursor.fetchall() 
                if row not in (None,()):
                    self.mset[rdpDbName]            = MainSet(mainname = rdpDbName)
                    mainset                         = self.mset[rdpDbName] 
                    for i in row:
                        #'gut_Endo_Ileum__Crohn__Healthy Control'
                        datasetname                 = i[self.RD.tab_results.datasetname]                         
                        #result_obj  -> RESULTS instance
                        result_obj                  = cPickle.loads(i[self.RD.tab_results.result_obj])
                        mainset.dset[datasetname]   = result_obj
                        mainset.dsort.append(datasetname)                        
                    mainset.dsort.sort()
                    for i in mainset.dsort:
                        nameAbr = self.getAbrName(i)
                        mainset.nameabr.append(nameAbr)
#                break
#            break
    
    def processSVM(self):
        for i in self.mset:
            mainset         = self.mset[i]
            dsort           = mainset.dsort
            dset            = mainset.dset
            for j in range(len(dsort)):
                dname           = dsort[j]
                result_obj      = dset[dname]
                datafact        = result_obj.datafact
                results_svm     = result_obj.results_svm
                runSVM          = False
                if results_svm != None:
                    #if results_svm exists,check flow.REsvm
                    if self.flow.REsvm:
                        #if flow.REsvm is True,do it, one by one
                        runSVM  = True
                    else:
                        continue
                else:
                    runSVM      = True
                if runSVM:
                    psvm = ProcessSVM(runLearner  = (SVMpm.Kern.RBF,)   ,
                                      drtn        = (
                                                     SVMpm.Drtn.NORM    ,
                                                     SVMpm.Drtn.B2UP    ,
                                                     )                  ,                            
                                      datafact    = datafact            ,
                                      )
                    psvm.mainRun()
                    #psvm.mainres -> RES instance
                    result_obj.results_svm  = psvm.mainres
                    result_obj_string       = MySQLdb.escape_string(cPickle.dumps(result_obj,2))
                    self.RD.query("""update %s set %s="%s" where %s="%s"                                                                                    
                                  """ % (self.RD.tab_results.tablename      ,  
                                         self.RD.tab_results.result_obj     ,
                                         result_obj_string                  ,
                                         self.RD.tab_results.datasetname    ,
                                         dname                              ,
                                         ))
    
    def processMSTAT(self):
        for i in self.mset:
            mainset         = self.mset[i]
            dsort           = mainset.dsort
            dset            = mainset.dset
            for j in range(len(dsort)):
                dname               = dsort[j]        
#                if dname != 'rdp8level3_Ileum_Lumen__Crohn_Ileum__Crohn_Lumen':
#                    continue
                print dname        
                result_obj          = dset[dname]
                datafact            = result_obj.datafact
                results_svm         = result_obj.results_svm
                results_mstat       = result_obj.results_mstat
                runMSTAT            = False
                if results_mstat != None:
                    if self.flow.REmstat == True:
                        runMSTAT    = True
                    else:
                        continue
                else:
                    runMSTAT        = True
                if runMSTAT:
                    pms             = ProcessMetastats(runot            = Mpm.run.both  ,                                       
                                                       pthreshold       = 0.08          , 
                                                       datafact         = datafact      ,
                                                       results_svm      = results_svm   ,
                                                       results_mstat    = results_mstat ,                                      
                                                       )
                    pms.mainRun()
                    #psvm.mainres -> RES instance
                    result_obj.results_mstat    = pms.mainres
                    result_obj_string           = MySQLdb.escape_string(cPickle.dumps(result_obj,2))
                    self.RD.query("""update %s set %s="%s" where %s="%s"                                                                                    
                                  """ % (self.RD.tab_results.tablename      ,  
                                         self.RD.tab_results.result_obj     ,
                                         result_obj_string                  ,
                                         self.RD.tab_results.datasetname    ,
                                         dname                              ,
                                         ))
                  
    def SVMXcelRecord(self):
        #For 'rdp8level1', crete folder named 'rdp8level1'
        for i in self.mset:
            #i -> rdp8level1
            #prefoler = \output\gutmtps
            prefolder       = os.path.join(self.output_folder,self.maindbname)
            try:
                os.mkdir(prefolder)
            except (WindowsError,):
                #Folder already exists
                pass
            outfolder       = os.path.join(prefolder,i)
            try:
                os.mkdir(outfolder)
            except (WindowsError,):
                #Folder already exists
                pass
            output_file     = os.path.join(outfolder,self.svmoutput_file)
            mainset         = self.mset[i]
            dsort           = mainset.dsort
            dset            = mainset.dset
            nameabr         = mainset.nameabr
            svmrp           = SVMReport(output_file  = output_file    ,
                                        dset         = dset           ,
                                        dsort        = dsort          ,
                                        nameabr      = nameabr        ,
                                        )
            svmrp.xcelRecord()
    
    def MSTATXcelRecord(self):
        #For 'rdp8level1', crete folder named 'rdp8level1'
        for i in self.mset:
            #i -> rdp8level1
            prefolder       = os.path.join(self.output_folder,self.maindbname)
            try:
                os.mkdir(prefolder)
            except (WindowsError,):
                #Folder already exists
                pass
            outfolder       = os.path.join(prefolder,i)
            try:
                os.mkdir(outfolder)
            except (WindowsError,):
                #Folder already exists
                pass
            output_file     = os.path.join(outfolder,self.mstatoutput_file)
            mainset         = self.mset[i]
            dsort           = mainset.dsort
            dset            = mainset.dset
            nameabr         = mainset.nameabr
            mstatrp         = MSTATReport(output_file  = output_file        ,
                                          pthreshold   = 0.08               ,
                                          dset         = dset               ,
                                          dsort        = dsort              ,
                                          nameabr      = nameabr            ,
                                          )
            mstatrp.xcelRecord()
            
    def featureXcelRecord(self):
        #For 'rdp8level1', crete folder named 'rdp8level1'
        for i in self.mset:
            #i -> rdp8level1
            prefolder       = os.path.join(self.output_folder,self.maindbname)
            try:
                os.mkdir(prefolder)
            except (WindowsError,):
                #Folder already exists
                pass
            outfolder       = os.path.join(prefolder,i)
            try:
                os.mkdir(outfolder)
            except (WindowsError,):
                #Folder already exists
                pass
            output_file     = os.path.join(outfolder,self.foutput_file)
            mainset         = self.mset[i]
            dsort           = mainset.dsort
            dset            = mainset.dset
            nameabr         = mainset.nameabr
            ftdisplay       = FeatureReport(output_file  = output_file        ,
                                            pthreshold   = 0.08               ,
                                            dset         = dset               ,
                                            dsort        = dsort              ,
                                            nameabr      = nameabr            ,
                                            )
            ftdisplay.xcelRecord()
            
    def formalXcelRecord(self):
        #For 'rdp8level1', crete folder named 'rdp8level1'
        for i in self.mset:
            #i -> rdp8level1
            prefolder       = os.path.join(self.output_folder,self.maindbname)
            try:
                os.mkdir(prefolder)
            except (WindowsError,):
                #Folder already exists
                pass
            outfolder       = os.path.join(prefolder,i)
            try:
                os.mkdir(outfolder)
            except (WindowsError,):
                #Folder already exists
                pass
            output_file     = os.path.join(outfolder,self.moutput_file)
            mainset         = self.mset[i]
            dsort           = mainset.dsort
            dset            = mainset.dset
            fm              = FormalReport(output_file  = output_file       ,
                                           pthreshold   = 0.08              ,
                                           dset         = dset              ,
                                           dsort        = dsort             ,
                                           )
            fm.xcelRecord()
    
    def comparisonXCelReport(self):
        self.lhpcr_rec  = GUTMTPSLHPCR()
        self.lhpcr_rec.gatherRecordInformation()
        output_file     = os.path.join(self.output_folder,self.coutput_file)
        fm              = ComparisonReport(output_file  = output_file       ,
                                           mset         = self.mset         ,
                                           gutlhpcr     = self.lhpcr_rec    ,
                                           )                                       
        fm.xcelRecord()
    
    def buildIndexTable(self):
        #a -> {'rdp8level1':[('A1','sulmonela'),..],..}
        mr = {}
        #index table name -> rdp8level1_fl,rdp8level7_fl
        for rdptype in GUTIBD.rdpType.LIST:
            #i -> '8'
            for rdp in GUTIBD.rdpList.LIST:
                #rdpDbName -> 'rdp8level1
                rdpDbName       = self.rdp2dbName(rdp,rdptype)                
                tf              = '%s_fl' % (rdpDbName,) 
                self.RD.addFLookup(tf)               
                try:
                    self.RD.query("""select * from %s 
                                  """ % (self.RD.tab_flookup[tf].tablename  ,))        
                except (MySQLdb.ProgrammingError,):
                    continue
                mr[rdpDbName]   = []
                row             = self.RD.cursor.fetchall() 
                if row not in (None,()):
                    for i in row:
                        #mfeature -> 'A1'
                        mfeature = i[self.RD.tab_flookup[tf].mfeature]
                        #rfeature -> 'Sulmonela'
                        rfeature = i[self.RD.tab_flookup[tf].rfeature]
                        mr[rdpDbName].append((mfeature,rfeature))
                    mr[rdpDbName].sort(lambda x,y: int(x[0][2:]) - int(y[0][2:]))
        #Write to excel 
        row_list    = ['Index','Feature']
        w_list      = [5000,15000]
        book        = xlwt.Workbook()
        file_save   = os.path.join(self.output_folder,self.flookup_file)
        c_col       = 0
        c_row       = 0                 
        for rdptype in GUTIBD.rdpType.LIST:
            #i -> '8'
            for rdp in GUTIBD.rdpList.LIST:
                self.c_col  = c_col
                self.c_row  = c_row
                rdpDbName   = self.rdp2dbName(rdp,rdptype)
                try:
                    mr[rdpDbName]
                except (KeyError,):
                    continue
                if len(mr[rdpDbName]) == 0:
                    continue
                sheetname   = rdpDbName.upper()
                sheet       = book.add_sheet(sheetname)
                #Set cell width
                for i in range(len(w_list)):
                    sheet.col(i).width   =   w_list[i]
                for i in row_list:
                    sheet.write(self.c_row,self.ci(),i)
                self.c_col  = c_col
                self.ri()
                for i in mr[rdpDbName]:
                    #i -> (mfeature,feature)
                    mfeature = i[0]
                    rfeature = i[1]
                    sheet.write(self.c_row,self.c_col       ,mfeature)
                    sheet.write(self.c_row,self.c_col + 1   ,rfeature)
                    self.ri()
        book.save(file_save)
        
    def getAbrName(self,dset):
        #datasetname -> 'gutmtpslhpcr_Colon_Lumen__Crohn_Colon__Crohn__Lumen'
        a               = dset.find('_')
        nameM           = dset[a+1:] #'Colon_Lumen__Crohn_Colon__Crohn_Lumen'
        nameA           = nameM.split('__')[0] # 'Colon_Lumen'
        nameB           = '__'.join(nameM.split('__')[1:]) # 'Crohn_Colon__Crohn_Lumen'    
        nameBA          = nameB.split('__')[0] # 'Crohn_Colon'
        nameBB          = nameB.split('__')[1] # 'Crohn_Lumen'
        #Process nameA
        nameA_p         = nameA.split('_')
        nameA_p_list    = []
        for i in nameA_p:
            #i -> 'Colon'
            abr = Abr[i]
            nameA_p_list.append(abr)
        nameA_abr       = '_'.join(nameA_p_list) #'C_LM'
        #Process nameBA
        nameBA_p        = nameBA.split('_')
        nameBA_p_list   = []
        for i in nameBA_p:
            #i -> 'Colon'
            abr = Abr[i]
            nameBA_p_list.append(abr)
        nameBA_abr      = '_'.join(nameBA_p_list) #'C_CO'
        #Process nameBB
        nameBB_p        = nameBB.split('_')
        nameBB_p_list   = []
        for i in nameBB_p:
            #i -> 'Colon'
            abr = Abr[i]
            nameBB_p_list.append(abr)
        nameBB_abr      = '_'.join(nameBB_p_list) #'C_LM'
        #Coming names
        nameAbr         = '__'.join([nameA_abr,nameBA_abr,nameBB_abr])
        return nameAbr


class RDPInfo(object):
    
    def __init__(self,**kwargs):
        self.rdpType        = None
        self.rdpDbName      = None
        self.abd_book       = None
        self.abd_sheet      = None
        self.outfile        = None
        self.bookwrite      = None  #xlwt.Workbook instance
        self.rdplevel       = {}    #{'RDP LEVEL 1': RDPLevel instance, ..}
        self.gutmtps        = None #GUTMTPS instance
        self.__dict__.update(kwargs)
        
    def data2orange(self):
        self.useSampleName  = self.gutmtps.useSampleName
        self.sSample        = self.gutmtps.sSample
        #Gather instance information
        self.SampleSites    = self.gutmtps.ssites_sheet
        #RUN
        self.RDP            = {} #{'RDP LEVEL 1':RDPLevel instance,..}
        #There are RDP 7 LEVELS, each level is RDPLevel instance
        #Basically, the dataset for each level is built
        self.RDP_Level      = GUTIBD.rdpList.LIST        
        #Match Sample id to its disease
        self.name2class     = {} #{'Dys100_19':Ulcerative Colitis, ..}
        #LINKED instance contains information about SampleSite,Diagnosis_X,Sex,Age
        self.Linked         = {} #{'Dys100_19':LINKED instance, ..} from IBD_Metadata_X.xls
        #SITES instance contains information on each site ex. Method(Endoscopic),Class_X(Ileum),Class_1(Iluem),
        #Class_2(Normal),Class_3(Non-Washed)
        self.Sites          = {} #{1:SITES object, ..} #From sample site details sheet
        #Inject self.Sites from self.SampleSites 
        for row in range(self.SampleSites.nrows):
            if row == 0: 
                continue
            value = [] #['1','Endoscopic','Ileum','Normal','Non-Washed','6 non-washed from normal terminal ileum']
            for col in range(self.SampleSites.ncols):
                value.append(str(self.SampleSites.cell(row,col).value))
            try:
                #Detect empty cell of the first column
                float(value[0])
                site    = str(int(float(value[0])))
            except (ValueError,):
                #If empty cell detected, skip to the next row
                continue
            for j in range(len(value)):
                try:
                    self.Sites[site]
                except (KeyError,):
                    self.Sites[site]            = SITES(Site = site)
                if   j == 1: #'Method' column
                    self.Sites[site].Method     = value[j]
                elif j == 2: #'Class X' column
                    self.Sites[site].Class_X    = value[j]
                elif j == 3: #'Class 1' column
                    self.Sites[site].Class_1    = value[j]
                elif j == 4: #'Class 2' column
                    self.Sites[site].Class_2    = value[j]
                elif j == 5: #'Class 3' column
                    self.Sites[site].Class_3    = value[j]
        #Inject self.Linked from self.meta_sheet(IBD_Metadata_X)
        for row in range(self.gutmtps.meta_sheet.nrows):
            if row == 0: 
                continue #ignore the first row
            value       = []
            for col in range(self.gutmtps.meta_sheet.ncols):
                #for each row, get all values from columns
                value.append(str(self.gutmtps.meta_sheet.cell(row,col).value).strip())
            #fingerId -> 'Dys100_19'
            fingerId    = value[2] #'Fingerprint ID' column
            if fingerId in ('Fingerprint ID','',):
                #Block invalid rows here by Fingerprint ID
                continue
            for j in range(len(value)):
                try:
                    self.Linked[fingerId]
                except (KeyError,):
                    self.Linked[fingerId]               = LINKED(FingerID = fingerId)
                if   j == 7:  #'Sample Site' column 
                    self.Linked[fingerId].SampleSite    = value[j]
                elif j == 16: #'Diagnosis X'
                    self.Linked[fingerId].Diagnosis_X   = value[j]
                elif j == 29: #'Age' column
                    self.Linked[fingerId].Age           = value[j]
                elif j == 30: #'Sex' column
                    self.Linked[fingerId].Sex           = value[j]
        #Process abundance sheet
        isLast  = False #indicate that the last level is reached
        row     = 0
        start   = False
        ok      = 1
        while ok:
            #if start is None, the system is not in recording mode
            firstCheck          = str(self.abd_sheet.cell(row,0).value).strip() #'RDP LEVEL 1'
            if firstCheck in self.RDP_Level:
                #self.RDP_Level -> ('RDP LEVEL 1','RDP LEVEL 2',..)
                rdp             = firstCheck #'RDP LEVEL 1'
                if rdp == 'RDP LEVEL 7':
                    isLast = True
                rdpDbName       = self.gutmtps.rdp2dbName(rdp,self.rdpType)
                #self.RDP       -> {'RDP LEVEL 1':RDPLevel instance,..}
                self.RDP[rdp]   = RDPLevel(maindbname   = self.gutmtps.maindbname   ,
                                           level        = rdp                       ,
                                           rdpDbName    = rdpDbName                 ,
                                           )
                start           = True
                startmark       = row #reset rowstart here
            elif firstCheck == 'NUMBR OF READS':
                #End reads of each RDP LEVEL 
                start           = False
                if isLast:
                    break
            if start == True:
                #start -> True,start recording
                #get data
                local       = [] #[0,0.23,0.5]
                col         = 0
                s           = 1
                #cellValue 
                cellValue   = str(self.abd_sheet.cell(row,col).value).strip()
                #If encounter empty space, do not process               
                if cellValue in self.RDP_Level + ('',):   
                    #Skip rows having 'RDP LEVEL 1','CUTOFF=','0.01' and an empty row after it              
                    s       = 0
                while s:
                    try:
                        dValue  = str(self.abd_sheet.cell(row,col).value).strip()
                    except (IndexError,):
                        break
                    if row == startmark + 2:
                        #if row having 'RDP10 PHYLUM','Dys100_19','Dys100_37',..
                        if col >= 1:
                            #col 0 -> RDP10 PHYLUM (for instance)
                            #if row is in RDP10 PHYLUM, for example, then get sample name (FingerprintID)
                            name    = dValue #'Dys100_19'     
                            #Filter out LHPCR here
                            if name not in self.sSample:
                                self.RDP[rdp].nameForb.append((col,name))
                            else:
                                try:
                                    #label -> 'Ulcerative Colitis'
                                    label                           = self.Linked[name].Diagnosis_X
                                    #rdp   -> 'RDP LEVEL 1',self.RDP[rdp] -> RDPLevel instance
                                    #.name2class -> {'Dys100_19':Ulcerative Colitis, ..}
                                    self.RDP[rdp].name2class[name]  = label
                                    try:
                                        #if classList does not have this label,append it
                                        #so .classList -> ['Crohns','Ulcerative Colitis','Healthy Control']
                                        self.RDP[rdp].classList.index(label)
                                    except (ValueError,):
                                        self.RDP[rdp].classList.append(label)
                                    #name -> 'Dys100_19',site -> '19'
                                    site = str(int(float(self.Linked[name].SampleSite)))
                                    #.metaSampleSite -> ['19','1',..] -> list of sites to match FingerprintID list                                
                                    self.RDP[rdp].metaSampleSite.append(site)
                                    #.metaSex -> ['F','M','M',..]
                                    self.RDP[rdp].metaSex.append(self.Linked[name].Sex)
                                    #.Age -> ['31','53','35',..]
                                    self.RDP[rdp].metaAge.append(self.Linked[name].Age)
                                    #.metaMethod -> ['Endoscopic sites',..]                                
                                    self.RDP[rdp].metaMethod.append(self.Sites[site].Method)
                                    #.metaClassX -> ['Ileum','Ascending_Colon',..]
                                    self.RDP[rdp].metaClassX.append(self.Sites[site].Class_X)
                                    #.metaClass1 -> ['Ileum','Ascending_Colon',..]
                                    self.RDP[rdp].metaClass1.append(self.Sites[site].Class_1)
                                    #.metaClass2 -> ['Normal','Disease',..]
                                    self.RDP[rdp].metaClass2.append(self.Sites[site].Class_2)
                                    #.metaClass3 -> ['Non-Washed','Washed',..]
                                    self.RDP[rdp].metaClass3.append(self.Sites[site].Class_3)                                                                                                                                                     
                                    #.sampleName -> ['Dys100_19', ..]
                                    self.RDP[rdp].sampleName.append(name)                                  
                                except (KeyError,):        
                                    #Name here does not exist in Rush_Only_MasterCOC    
                                    #from self.Linked[name].Diagnosis_X because self.Linked get from
                                    #Rush_Only_MasterCOC
                                    #.nameColP -> [(0,Dys100_19),..] means Dys100_10 does not exist in Metadata sheet
                                    self.RDP[rdp].nameColP.append((col,name))                  
                    else:
                        #Row below 'RDP10 PHYLUM','Dys100_19','Dys100_37',.. and above NUMBR OF READS,.. 
                        if col == 0:
                            #Record feature names
                            featureRaw  = dValue #'Acidobacteria' -> Feature Name
                            self.RDP[rdp].featuresName.append(featureRaw)
                        else:
                            ColP    = [u[0] for u in self.RDP[rdp].nameColP]
                            Forb    = [u[0] for u in self.RDP[rdp].nameForb]
                            CFC     = ColP + Forb
                            #Record abundance values
                            if col not in CFC:
                                #if col valid (FingerprintID exists),append value
                                value       = float(dValue) #0.125
                                local.append(value)
                    col += 1
                    if dValue == '':
                        #break from each row
                        s = 0
                if local != []:   
                    #preSample -> list of list of values of feature (from feature straight down) 
                    self.RDP[rdp].preSample.append(local)                                            
            row += 1


class SITES(object):
    
    def __init__(self,**kwargs):
        self.Site           = None
        self.Method         = None
        self.Class_X        = None
        self.Class_1        = None
        self.Class_2        = None
        self.Class_3        = None
        self.__dict__.update(kwargs)


class LINKED(object):
    
    def __init__(self,**kwargs):
        self.FingerID       = None
        self.SampleSite     = None
        self.Diagnosis_X    = None
        self.Sex            = None
        self.Age            = None
        self.__dict__.update(kwargs)


class MainSet(object):
    
    def __init__(self,**kwargs):
        #self.mainname -> 'rdp10level1' 
        self.mainname       = ''
        self.dset           = {} #{'name': RESULTS instance, ..}
        self.dsort          = [] #List of datasetname
        self.nameabr        = [] #List of abr names
        self.__dict__.update(kwargs)

